package com.unis.common.util;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/*************************************************************************
 Copyright (C) Unpublished Unis Software, Inc. All rights reserved.
 Unis Software, Inc., Confidential and Proprietary.

 This software is subject to copyright protection
 under the laws of the Public of China and other countries.

 Unless otherwise explicitly stated, this software is provided
 by Unis "AS IS".
 *************************************************************************/

/**
 * @author CSJ (email:raulcsj@163.com)
 * @since 2012-12-7
 */
public final class SpyUtil {
    private SpyUtil() {}

    public static class TableMetaData {
        private String tableName = "";
        private String tableType = "";
        private String tableRemark = "";

        private List<String> primaryKeys = new ArrayList<String>();
        private List<ColumnMetaData> columns = new ArrayList<ColumnMetaData>();

        public String getTableName() {
            return tableName;
        }

        public void setTableName(String tableName) {
            this.tableName = tableName;
        }

        public String getTableType() {
            return tableType;
        }

        public void setTableType(String tableType) {
            this.tableType = tableType;
        }

        public String getTableRemark() {
            return tableRemark;
        }

        public void setTableRemark(String tableRemark) {
            this.tableRemark = tableRemark;
        }

        public void setPrimaryKeys(List<String> primaryKeys) {
            this.primaryKeys = primaryKeys;
        }

        public void setColumns(List<ColumnMetaData> columns) {
            this.columns = columns;
        }

        public List<ColumnMetaData> getColumns() {
            return columns;
        }

        public List<String> getPrimaryKeys() {
            return primaryKeys;
        }

        @Override
        public String toString() {
            return "TableMetaData [tableName=" + tableName + ", tableType=" + tableType + ", tableRemark="
                    + tableRemark + ", primaryKeys=" + primaryKeys + ", columns=" + columns + "]";
        }

    }

    public static class ColumnMetaData {
        private String columnName = "";
        private String columnType = "";
        private String columnRemark = "";

        public String getColumnName() {
            return columnName;
        }

        public void setColumnName(String columnName) {
            this.columnName = columnName;
        }

        public String getColumnType() {
            return columnType;
        }

        public void setColumnType(String columnType) {
            this.columnType = columnType;
        }

        public String getColumnRemark() {
            return columnRemark;
        }

        public void setColumnRemark(String columnRemark) {
            this.columnRemark = columnRemark;
        }

        @Override
        public String toString() {
            return "ColumnMetaData [columnName=" + columnName + ", columnType=" + columnType + ", columnRemark="
                    + columnRemark + "]";
        }

    }

    private String checkDBColumnType(int type) {
        String typeName = "";
        switch (type) {
        case Types.BIGINT:
        case Types.INTEGER:
        case Types.SMALLINT:
        case Types.TINYINT:
            typeName = "int";
            break;
        case Types.DECIMAL:
        case Types.DOUBLE:
        case Types.FLOAT:
        case Types.NUMERIC:
            typeName = "double";
            break;
        case Types.CHAR:
        case Types.CLOB:
        case Types.VARCHAR:
            typeName = "String";
            break;
        default:
            typeName = "byte[]";
            break;
        }
        return typeName;
    }

    public static TableMetaData spyDB(String tableName) throws Exception {
        InputStream in = SpyUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties props = new Properties();
        props.load(in);

        String driver = props.getProperty("jdbc.driverClassName");
        String url = props.getProperty("jdbc.url");

        props.put("user", props.get("jdbc.username"));
        props.put("password", props.get("jdbc.password"));
        props.put("remarksReporting", "true");// 取出common设置

        Class.forName(driver);
        Connection conn = DriverManager.getConnection(url, props);
        DatabaseMetaData metaData = conn.getMetaData();
        String[] tableTypes = { "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS",
                "SYSNONYM" };
        ResultSet tRs = metaData.getTables(null, "%", tableName, tableTypes);
        TableMetaData tableMetaData = new TableMetaData();
        while (tRs.next()) {
            tableMetaData.setTableName(tRs.getString("TABLE_NAME").toLowerCase());
            tableMetaData.setTableType(tRs.getString("TABLE_TYPE"));
            tableMetaData.setTableRemark(tRs.getString("REMARKS"));
        }

        ResultSet cRs = metaData.getColumns(null, "%", tableName, "%");
        SpyUtil spyDB = new SpyUtil();

        while (cRs.next()) {
            String columnType = "";
            String columnName = cRs.getString("COLUMN_NAME");
            String remark = cRs.getString("REMARKS");
            int dataType = cRs.getInt("DATA_TYPE");
            columnType = spyDB.checkDBColumnType(dataType);
            ColumnMetaData columnMetaData = new ColumnMetaData();
            columnMetaData.setColumnName(columnName.toLowerCase());
            columnMetaData.setColumnType(columnType);
            columnMetaData.setColumnRemark(remark);
            tableMetaData.getColumns().add(columnMetaData);
        }
        ResultSet pRs = metaData.getPrimaryKeys(null, "%", tableName);
        while (pRs.next()) {
            tableMetaData.getPrimaryKeys().add(pRs.getString("COLUMN_NAME").toLowerCase());
        }
        tRs.close();
        cRs.close();
        pRs.close();
        conn.close();
        return tableMetaData;
    }
}
